/**
 * 
 */
package com.dingding.order.core.myorder.sql;

import java.io.Serializable;

/**
 * @ClassName: MyOrderSQL
 * @Description: 我的订单SQL
 * @author zhouxuan
 * @date 2016年7月5日 上午10:44:42
 * @since V2.3.0
 */

@SuppressWarnings("serial")
public class MyOrderSQL implements Serializable {
	// 获取我的订单列表
	public static final String GET_MY_ORDER_LIST = "SELECT uo.cancelNumber, uo.cancelUserId, uo.cancelType, uo.couponMoney, uo.createTime, uo.distance, uo.driverRcvIntegral, uo.endAddress, uo.isCarShare, uo.isComment, uo.isLongDistance, uo.message, ut.nickName, uo.number, uo.oid, uo.orderType, uo.publishType, uo.setOutTime, uo.startAddress, uo.totalMoney, ut.userAvatar, ut.userLevel, ut.userTabloidAvatar,uo.placeOrderUserId FROM userorder uo INNER JOIN userdetail ut ON ut.loginId = CASE WHEN (uo.placeOrderUserId = ? AND uo.grabOrderUserId IS NOT NULL) THEN uo.grabOrderUserId ELSE uo.placeOrderUserId END WHERE uo.placeOrderUserId = ? OR uo.grabOrderUserId = ? ORDER BY uo.createTime DESC LIMIT ?,?";

	// 获取出租车司机订单列表
	public static final String GET_MY_DRIVER_ORDER_LIST = "SELECT uo.cancelNumber, uo.cancelUserId, uo.cancelType, uo.couponMoney, uo.createTime, uo.distance, uo.driverRcvIntegral, uo.endAddress, uo.isCarShare, uo.isComment, uo.isLongDistance, uo.message, ut.nickName, uo.number, uo.oid, uo.orderType, uo.publishType, uo.setOutTime, uo.startAddress, uo.totalMoney, ut.userAvatar, ut.userLevel, ut.userTabloidAvatar,uo.placeOrderUserId,uo.orderStatus FROM userorder uo INNER JOIN userdetail ut ON ut.loginId = CASE WHEN (uo.placeOrderUserId = ? AND uo.grabOrderUserId IS NOT NULL) THEN uo.grabOrderUserId ELSE uo.placeOrderUserId END WHERE uo.placeOrderUserId = ? OR uo.grabOrderUserId = ? ORDER BY uo.createTime DESC LIMIT ?,?";

	// 获取用户搭乘和接乘信息
	public static final String GET_USER_TAKE_IMFORMATION = "SELECT ud.totalWeldingProcess,ud.totalTake FROM userdetail ud WHERE ud.loginId= ?;";

	/**
	 * @Description:获取乘客的专车订单sql
	 * @param type
	 *            1 已完成的订单 2 未完成的订单
	 * @author fengshuonan
	 * @since 2.4
	 */
	public static final String getCustomerSpecialOrderInfo(int type) {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(
				"SELECT sui.userAvatar,sui.userTabloidAvatar,sul.userName,sui.nickName,sot.orderStatus,sot.specialOrderId as orderId, ");
		stringBuffer.append("suv.lisencePlate,suv.vehicleType,suv.vehicleModel,suv.vehicleColor, ");
		if (type == 1) {
			stringBuffer.append(
					"sot.setOutTime,sot.startAddress,sot.endAddress,sot.totalMoney as estimateMoney FROM userspecialorder sot ");
		} else {
			stringBuffer.append(
					"sot.setOutTime,sot.startAddress,sot.endAddress,sot.totalMoney as estimateMoney FROM userspecialordert sot ");
		}
		stringBuffer.append("LEFT JOIN specialuserlogin sul ON sot.grabOrderUserId = sul.suid ");
		stringBuffer.append("LEFT JOIN specialuserinfo sui ON sot.grabOrderUserId = sui.userId ");
		stringBuffer.append("LEFT JOIN specialuservehicleinfo suv ON sot.grabOrderUserId = suv.userId ");
		stringBuffer.append("WHERE sot.placeOrderUserId = ?");
		return stringBuffer.toString();
	}

	// 历史订单列表(顺风车 + 出租车)
	public static final String GET_HISTORY_ORDER_LIST = "SELECT u.oid,u.setOutTime,u.endAddress,u.cancelTime,u.endTime,u.isCarShare,u.isLongDistance,u.isComment,u.orderStatus,u.publishType,u.startAddress,u.totalMoney,CASE WHEN (u.placeOrderUserId = ? AND u.orderType = 1) THEN 1 WHEN (u.grabOrderUserId = ? AND u.orderType = 2) THEN 1 ELSE 2  END AS orderType FROM userorder u WHERE (u.placeOrderUserId = ? OR u.grabOrderUserId = ?) AND u.grabOrderUserId IS NOT NULL ";

	// 历史订单列表(专车)
	public static final String GET_SPECIAL_HISTORY_ORDER_LIST = "SELECT d.`vehicleLevelName` vehicleLevelName, u.specialOrderId AS oid,u.cancelTime,u.endTime,u.setOutTime,u.endAddress,u.isComment,u.orderStatus,u.publishType,u.startAddress,u.totalMoney,CASE WHEN u.placeOrderUserId = ? THEN 2 ELSE 1 END AS orderType FROM userspecialorder u,`dd_vehicle_level` d WHERE (u.placeOrderUserId = ? OR u.grabOrderUserId = ?) AND u.grabOrderUserId IS NOT NULL AND d.vehicleLevelId = u.vehicleLevelId";

	// 订单详情
	public static final String HISTORY_ORDER_DETAIL_LIST = "SELECT u.couponMoney,u.createTime,u.distance,u.driverRcvIntegral,u.endAddress,u.endTime,u.cancelTime,o.evaluateLabel,o.evaluateLevel,u.isComment,v.lisencePlate,u.message,udl.nickName,u.number,u.oid,u.publishType,u.setOutTime,u.startAddress,u.totalMoney,udl.userAvatar,udl.userLevel,udl.userTabloidAvatar,v.vehicleColor,v.vehicleModel,v.vehicleType,CASE WHEN (u.placeOrderUserId = ? AND u.orderType = 1) THEN 1 WHEN (u.grabOrderUserId = ? AND u.orderType = 2) THEN 1 ELSE 2   END AS orderType FROM userorder u LEFT JOIN ordercomment o ON o.orderId = u.oid LEFT JOIN vehicleinfo v ON v.loginId = CASE WHEN u.orderType = 1 THEN u.placeOrderUserId ELSE u.grabOrderUserId END  LEFT JOIN userdetail udl ON udl.loginId = CASE WHEN u.placeOrderUserId = ? AND u.orderType = 1 THEN u.placeOrderUserId WHEN u.grabOrderUserId  = ? AND u.orderType = 2 THEN u.grabOrderUserId WHEN u.placeOrderUserId = ? AND u.orderType = 2 THEN u.grabOrderUserId WHEN u.grabOrderUserId = ? AND u.orderType = 1 THEN u.placeOrderUserId END  WHERE u.oid = ? ";
	// 专车订单详情
	public static final String SPECIAL_HISTORY_ORDER_DETAIL_LIST = "SELECT u.cancelType,u.createTime,u.distance,u.duration,u.endAddress,u.endTime,u.cancelTime,s.evaluateLabel,s.evaluateLevel,u.isComment,sv.lisencePlate,sui.nickName,u.specialOrderId AS oid,u.publishType,u.setOutTime,u.startAddress,u.totalMoney,sui.userAvatar,sui.userLevel,sui.userTabloidAvatar,sv.vehicleColor,sv.vehicleModel,sv.vehicleType,CASE WHEN u.placeOrderUserId = ? THEN 2 ELSE 1 END AS orderType FROM userspecialorder u LEFT JOIN specialordercomment s ON s.specialOrderId = u.specialOrderId LEFT JOIN specialuservehicleinfo sv ON sv.userId = u.grabOrderUserId LEFT JOIN specialuserinfo sui ON sui.userId = u.grabOrderUserId WHERE u.specialOrderId = ?";

	// 专车订单详情
	public static final String TAXI_HISTORY_ORDER_DETAIL_LIST = "SELECT u.cancelType,u.createTime,u.distance,u.endAddress,u.endTime,u.cancelTime,s.evaluateLabel,s.evaluateLevel,s.evaluateContent,u.isComment,sv.lisencePlate,sui.nickName,u.oid AS oid,u.publishType,u.setOutTime,u.startAddress,u.totalMoney,sui.userAvatar,sui.userLevel,sui.userTabloidAvatar,sv.vehicleColor,sv.vehicleModel,sv.vehicleType,CASE WHEN u.placeOrderUserId = ? THEN 2 ELSE 1 END AS orderType FROM userorder u LEFT JOIN ordercomment s ON s.orderId = u.oid LEFT JOIN specialuservehicleinfo sv ON sv.userId = u.grabOrderUserId LEFT JOIN specialuserinfo sui ON sui.userId = u.grabOrderUserId WHERE u.oid = ?";

}
